SET NOCOUNT ON;
declare @release smallint = 6;
declare @version smallint = 0;
declare @script_name varchar(256);
declare @script_type varchar(20)='CensusData';
set @script_name=@script_type+'_V'+convert(varchar(5),@version)+'R'+right('00000'+convert(varchar(5),@release),5)+'.sql'

if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=0)
	print 'script '+@script_name+' already executed, please undo then re-execute.';
else
begin try
begin tran
/*--Begin Code Here --*/

declare @row_count int=0;
declare @expected_row_count int=-1;

select @row_count=count(*) from ACSSF.Geography_File;
select @expected_row_count=Expected_Record_Count from dbo.ACSSF_Load_Control where Table_Name='Geography_File' and Sample_Set_Code='2013e1';
if @row_count<>@expected_row_count 
	RAISERROR('Error in loading Geography_File, Row Count%d: vs Expected Row Count:%d',16,1,@row_count,@expected_row_count)
else
	update dbo.ACSSF_Load_Control 
	set Record_Count = @row_count
	,Validated_Date =getdate()
	,Load_Successful_Flag =1
	where
	Table_Name='Geography_File'
	and
	Sample_Set_Code='2013e1'
	;


select @row_count=count(*) from ACSSF.Summary_File_0001;
select @expected_row_count=Expected_Record_Count from dbo.ACSSF_Load_Control where Table_Name='Summary_File_0001' and Sample_Set_Code='2013e1';
if @row_count<>@expected_row_count 
	RAISERROR('Error in loading Geography_File, Row Count%d: vs Expected Row Count:%d',16,1,@row_count,@expected_row_count)
else
	update dbo.ACSSF_Load_Control 
	set Record_Count = @row_count
	,Validated_Date =getdate()
	,Load_Successful_Flag =1
	where
	Table_Name='Summary_File_0001'
	and
	Sample_Set_Code='2013e1'
	;

if not exists (select 1 from sys.columns where name='FILETYPE' and object_id=object_id('ACSSF.Geography_File'))
	alter table ACSSF.Geography_File
	add FILETYPE CHAR(6) NULL;


/*--End Code Here --*/
if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=1)
	update dbo.database_version_information 
	set undone=0
	,date_released=getdate()
	where
	script_name=@script_name;
else
	insert into dbo.database_version_information 
	values(@script_name,@script_type,@version,@release,GETDATE(),0);
	
print 'script '+@script_name+' succeeded!'
commit tran
end try
begin catch 
	declare @error_message varchar(2000);
	set @error_message='SCRIPT:'+@script_name+' FAILED! ROLLBACK!  ERROR_MESSAGE:'
	+coalesce(error_message(),'')+' ERROR_NUMBER:'+coalesce(convert(varchar(10),error_number()),'');
	ROLLBACK
	raiserror(@error_message,16,1)
end catch
